#!/usr/bin/python3
import pymysql
#什么是 PyMySQL？
#PyMySQL 是在 Python3.x 版本中用于连接 MySQL 服务器的一个库，Python2中则使用mysqldb。
#PyMySQL 遵循 Python 数据库 API v2.0 规范，并包含了 pure-Python MySQL 客户端库。

#首先，升级pip版本，然后使用pip命令安装PyMySQL
#sudo pip install --upgrade pip
#sudo pip install PyMySQL

#测试连接
#打开数据库连接
db = pymysql.connect("localhost", "root", "root", "testdb")
#使用cursor()方法创建一个游标对象cursor
cursor = db.cursor()
#使用execute()方法执行SQL查询
cursor.execute("select version()")
#使用fetchone()方法获取单条数据
data = cursor.fetchone()
print("Database version: %s" % data)
#关闭数据库连接
#db.close()

#创建表
cursor.execute("drop table if exists employee")
#使用预处理语句创建表
sql = """create table employee(
    first_name varchar(20) not null,
    last_name varchar(20),
    age int, 
    sex char(5),
    income float
)"""
print("create table here")
try:
    cursor.execute(sql)
except:
    print('create failed')

    
#插入数据
sql = """insert into employee(first_name,
    last_name,age,sex,income)
    values
    ('Rudd','Howl',22,'F',30000),
    ('TOM','Jack',20,'M',2000)"""
print("Insert now")
try:
    #执行sql语句
    cursor.execute(sql)
    #提交到数据库执行
    db.commit()
except:
    #如果发生错误则回滚
    db.rollback()
    print("insert failed")
    #没有消息就是最好的消息
print("%d rows affected" % cursor.rowcount)

#查询数据
#Python查询Mysql使用 fetchone() 方法获取单条数据, 使用fetchall() 方法获取多条数据。
#fetchont(): 该方法获取下一个查询结果集。结果集是一个对象
#fetchall(): 接收全部的返回结果行。
#rowcount: 这是一个只读属性，并返回execute()返回后影响的行数。
sql = "select * from employee\
    where age > '%d'" % (18)
print("Select now")
try:
    #执行sql语句
    cursor.execute(sql)
    #获取所有记录列表
    results = cursor.fetchall()
    for row in results:
        first_name = row[0]
        last_name = row[1]
        age = row[2]
        sex = row[3]
        income = row[4]
        #打印结果
        print("fname = %s, lname = %s, age = %d, sex = %s, income = %.2f" %\
            (first_name, last_name, age, sex, income))
except:
    print("Error: unable to fetch data")
#cursor中的rowcount属性，记录影响的行数
print("%d rows affected" % cursor.rowcount)

#更新数据
# SQL 更新语句  
sql = "update employee set age = age + 1 where sex = '%c'" % ('M')  
print("Update now")
try:  
   # 执行SQL语句  
   cursor.execute(sql)  
   # 提交到数据库执行  
   db.commit()  
except:  
   # 发生错误时回滚  
   db.rollback() 
   print("update failed")
print("%d rows affected" % cursor.rowcount)

# SQL 删除语句  
sql = "delete from employee where age < '%d'" % (22)  
print("Delete now")
try:  
   # 执行SQL语句  
   cursor.execute(sql)  
   # 提交修改  
   db.commit()  
except:  
   # 发生错误时回滚  
   db.rollback()  
   print("delete failed")
print("%d rows affected" % cursor.rowcount)

#执行事务
#事务机制可以确保数据一致性。
#事务应该具有4个属性：原子性、一致性、隔离性、持久性。这四个属性通常称为ACID特性。
#原子性（atomicity）。一个事务是一个不可分割的工作单位，事务中包括的操作要么都做，要么都不做。
#一致性（consistency）。事务必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。
#隔离性（isolation）。一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。
#持久性（durability）。持续性也称永久性（permanence），指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。
#Python DB API 2.0 的事务提供了两个方法 commit 或 rollback。

# SQL删除记录语句  
sql = "delete from employee where age > '%d'" % (30)  
try:  
   # 执行SQL语句  
   cursor.execute(sql)  
   # 向数据库提交  
   db.commit()  
except:  
   # 发生错误时回滚  
   db.rollback() 
   print("Error happened")
#对于支持事务的数据库， 在Python数据库编程中，当游标建立之时，就自动开始了一个隐形的数据库事务。
#commit()方法游标的所有更新操作，rollback()方法回滚当前游标的所有操作。每一个方法都开始了一个新的事务。

#关闭数据库连接
db.close()
